
-----------------------------------------------------------------------
-- actions
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'actions')
BEGIN
    DECLARE @reftable_1 nvarchar(60), @constraintname_1 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'actions'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_1, @constraintname_1
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_1+' drop constraint '+@constraintname_1)
        FETCH NEXT from refcursor into @reftable_1, @constraintname_1
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE actions
END

CREATE TABLE actions
(
    id INT NOT NULL IDENTITY,
    name VARCHAR(45) NOT NULL,
    code VARCHAR(10) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT actions_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- attachments
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_attachments_car_damages1')
    ALTER TABLE attachments DROP CONSTRAINT fk_attachments_car_damages1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_attachments_dict_attachment_types1')
    ALTER TABLE attachments DROP CONSTRAINT fk_attachments_dict_attachment_types1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'attachments')
BEGIN
    DECLARE @reftable_2 nvarchar(60), @constraintname_2 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'attachments'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_2, @constraintname_2
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_2+' drop constraint '+@constraintname_2)
        FETCH NEXT from refcursor into @reftable_2, @constraintname_2
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE attachments
END

CREATE TABLE attachments
(
    id INT NOT NULL IDENTITY,
    dict_attachment_type_id INT NOT NULL,
    car_damage_id INT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT attachments_PK PRIMARY KEY (id)
);

CREATE INDEX fk_attachments_car_damages1_idx ON attachments (car_damage_id);

CREATE INDEX fk_attachments_dict_attachment_types1_idx ON attachments (dict_attachment_type_id);

BEGIN
ALTER TABLE attachments ADD CONSTRAINT fk_attachments_car_damages1 FOREIGN KEY (car_damage_id) REFERENCES car_damages (id)
END
;

BEGIN
ALTER TABLE attachments ADD CONSTRAINT fk_attachments_dict_attachment_types1 FOREIGN KEY (dict_attachment_type_id) REFERENCES dict_attachment_types (id)
END
;

-----------------------------------------------------------------------
-- car_damage_change_logs
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_car_damage_change_logs_car_damages1')
    ALTER TABLE car_damage_change_logs DROP CONSTRAINT fk_car_damage_change_logs_car_damages1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'car_damage_change_logs')
BEGIN
    DECLARE @reftable_3 nvarchar(60), @constraintname_3 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'car_damage_change_logs'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_3, @constraintname_3
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_3+' drop constraint '+@constraintname_3)
        FETCH NEXT from refcursor into @reftable_3, @constraintname_3
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE car_damage_change_logs
END

CREATE TABLE car_damage_change_logs
(
    id INT NOT NULL IDENTITY,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    car_damages_id INT NOT NULL,
    name VARCHAR(45) NULL,
    old_value VARCHAR(45) NULL,
    new_value VARCHAR(45) NULL,
    CONSTRAINT car_damage_change_logs_PK PRIMARY KEY (id)
);

CREATE INDEX fk_car_damage_change_logs_car_damages1_idx ON car_damage_change_logs (car_damages_id);

BEGIN
ALTER TABLE car_damage_change_logs ADD CONSTRAINT fk_car_damage_change_logs_car_damages1 FOREIGN KEY (car_damages_id) REFERENCES car_damages (id)
END
;

-----------------------------------------------------------------------
-- car_damages
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_car_damages_car_damages1')
    ALTER TABLE car_damages DROP CONSTRAINT fk_car_damages_car_damages1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_car_damages_dict_statuses1')
    ALTER TABLE car_damages DROP CONSTRAINT fk_car_damages_dict_statuses1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_car_damages_car_data1')
    ALTER TABLE car_damages DROP CONSTRAINT fk_car_damages_car_data1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_car_damages_users1')
    ALTER TABLE car_damages DROP CONSTRAINT fk_car_damages_users1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_car_damages_users2')
    ALTER TABLE car_damages DROP CONSTRAINT fk_car_damages_users2;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_car_damages_dict_perpetrators1')
    ALTER TABLE car_damages DROP CONSTRAINT fk_car_damages_dict_perpetrators1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_car_damages_process_types1')
    ALTER TABLE car_damages DROP CONSTRAINT fk_car_damages_process_types1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_car_damages_users3')
    ALTER TABLE car_damages DROP CONSTRAINT fk_car_damages_users3;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'car_damages')
BEGIN
    DECLARE @reftable_4 nvarchar(60), @constraintname_4 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'car_damages'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_4, @constraintname_4
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_4+' drop constraint '+@constraintname_4)
        FETCH NEXT from refcursor into @reftable_4, @constraintname_4
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE car_damages
END

CREATE TABLE car_damages
(
    id INT NOT NULL IDENTITY,
    car_data_id INT NOT NULL,
    status_id INT NOT NULL,
    dict_perpetrator_id INT NOT NULL,
    process_type_id INT NOT NULL,
    related_car_damage INT NULL,
    dealer_id INT NULL,
    ls_id INT NULL,
    ol_id INT NULL,
    damage_date DATETIME NULL,
    dealer_report_date DATETIME NULL,
    perpetrator_report_date DATETIME NULL,
    damages_description VARCHAR(MAX) NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT car_damages_PK PRIMARY KEY (id)
);

CREATE INDEX fk_car_damages_car_damages1_idx ON car_damages (related_car_damage);

CREATE INDEX fk_car_damages_dict_statuses1_idx ON car_damages (status_id);

CREATE INDEX fk_car_damages_car_data1_idx ON car_damages (car_data_id);

CREATE INDEX fk_car_damages_users1_idx ON car_damages (ls_id);

CREATE INDEX fk_car_damages_users2_idx ON car_damages (ol_id);

CREATE INDEX fk_car_damages_dict_perpetrators1_idx ON car_damages (dict_perpetrator_id);

CREATE INDEX fk_car_damages_process_types1_idx ON car_damages (process_type_id);

CREATE INDEX fk_car_damages_users3_idx ON car_damages (dealer_id);

BEGIN
ALTER TABLE car_damages ADD CONSTRAINT fk_car_damages_car_damages1 FOREIGN KEY (related_car_damage) REFERENCES car_damages (id)
END
;

BEGIN
ALTER TABLE car_damages ADD CONSTRAINT fk_car_damages_dict_statuses1 FOREIGN KEY (status_id) REFERENCES dict_statuses (id)
END
;

BEGIN
ALTER TABLE car_damages ADD CONSTRAINT fk_car_damages_car_data1 FOREIGN KEY (car_data_id) REFERENCES car_data (id)
END
;

BEGIN
ALTER TABLE car_damages ADD CONSTRAINT fk_car_damages_users1 FOREIGN KEY (ls_id) REFERENCES users (id)
END
;

BEGIN
ALTER TABLE car_damages ADD CONSTRAINT fk_car_damages_users2 FOREIGN KEY (ol_id) REFERENCES users (id)
END
;

BEGIN
ALTER TABLE car_damages ADD CONSTRAINT fk_car_damages_dict_perpetrators1 FOREIGN KEY (dict_perpetrator_id) REFERENCES dict_perpetrators (id)
END
;

BEGIN
ALTER TABLE car_damages ADD CONSTRAINT fk_car_damages_process_types1 FOREIGN KEY (process_type_id) REFERENCES process_types (id)
END
;

BEGIN
ALTER TABLE car_damages ADD CONSTRAINT fk_car_damages_users3 FOREIGN KEY (dealer_id) REFERENCES users (id)
END
;

-----------------------------------------------------------------------
-- car_data
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_car_data_dict_brands1')
    ALTER TABLE car_data DROP CONSTRAINT fk_car_data_dict_brands1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'car_data')
BEGIN
    DECLARE @reftable_5 nvarchar(60), @constraintname_5 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'car_data'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_5, @constraintname_5
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_5+' drop constraint '+@constraintname_5)
        FETCH NEXT from refcursor into @reftable_5, @constraintname_5
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE car_data
END

CREATE TABLE car_data
(
    id INT NOT NULL IDENTITY,
    dict_brand_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    vin VARCHAR(45) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT car_data_PK PRIMARY KEY (id)
);

CREATE INDEX fk_car_data_dict_brands1_idx ON car_data (dict_brand_id);

BEGIN
ALTER TABLE car_data ADD CONSTRAINT fk_car_data_dict_brands1 FOREIGN KEY (dict_brand_id) REFERENCES dict_brands (id)
END
;

-----------------------------------------------------------------------
-- damages
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_damages_car_damages1')
    ALTER TABLE damages DROP CONSTRAINT fk_damages_car_damages1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_damages_dict_damage_types1')
    ALTER TABLE damages DROP CONSTRAINT fk_damages_dict_damage_types1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_damages_dict_damage_spots1')
    ALTER TABLE damages DROP CONSTRAINT fk_damages_dict_damage_spots1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_damages_dict_damage_scale1')
    ALTER TABLE damages DROP CONSTRAINT fk_damages_dict_damage_scale1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'damages')
BEGIN
    DECLARE @reftable_6 nvarchar(60), @constraintname_6 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'damages'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_6, @constraintname_6
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_6+' drop constraint '+@constraintname_6)
        FETCH NEXT from refcursor into @reftable_6, @constraintname_6
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE damages
END

CREATE TABLE damages
(
    id INT NOT NULL IDENTITY,
    car_damage_id INT NOT NULL,
    dict_damage_type_id INT NOT NULL,
    dict_damage_spot_id INT NOT NULL,
    dict_damage_scale_id INT NOT NULL,
    is_main INT DEFAULT 1 NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT damages_PK PRIMARY KEY (id)
);

CREATE INDEX fk_damages_car_damages1_idx ON damages (car_damage_id);

CREATE INDEX fk_damages_dict_damage_types1_idx ON damages (dict_damage_type_id);

CREATE INDEX fk_damages_dict_damage_spots1_idx ON damages (dict_damage_spot_id);

CREATE INDEX fk_damages_dict_damage_scale1_idx ON damages (dict_damage_scale_id);

BEGIN
ALTER TABLE damages ADD CONSTRAINT fk_damages_car_damages1 FOREIGN KEY (car_damage_id) REFERENCES car_damages (id)
END
;

BEGIN
ALTER TABLE damages ADD CONSTRAINT fk_damages_dict_damage_types1 FOREIGN KEY (dict_damage_type_id) REFERENCES dict_damage_types (id)
END
;

BEGIN
ALTER TABLE damages ADD CONSTRAINT fk_damages_dict_damage_spots1 FOREIGN KEY (dict_damage_spot_id) REFERENCES dict_damage_spots (id)
END
;

BEGIN
ALTER TABLE damages ADD CONSTRAINT fk_damages_dict_damage_scale1 FOREIGN KEY (dict_damage_scale_id) REFERENCES dict_damage_scale (id)
END
;

-----------------------------------------------------------------------
-- dealers
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_dealers_users1')
    ALTER TABLE dealers DROP CONSTRAINT fk_dealers_users1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'dealers')
BEGIN
    DECLARE @reftable_7 nvarchar(60), @constraintname_7 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'dealers'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_7, @constraintname_7
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_7+' drop constraint '+@constraintname_7)
        FETCH NEXT from refcursor into @reftable_7, @constraintname_7
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE dealers
END

CREATE TABLE dealers
(
    id INT NOT NULL,
    user_id INT NOT NULL,
    CONSTRAINT dealers_PK PRIMARY KEY (id)
);

CREATE INDEX fk_dealers_users1_idx ON dealers (user_id);

BEGIN
ALTER TABLE dealers ADD CONSTRAINT fk_dealers_users1 FOREIGN KEY (user_id) REFERENCES users (id)
END
;

-----------------------------------------------------------------------
-- dict_attachment_types
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'dict_attachment_types')
BEGIN
    DECLARE @reftable_8 nvarchar(60), @constraintname_8 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'dict_attachment_types'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_8, @constraintname_8
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_8+' drop constraint '+@constraintname_8)
        FETCH NEXT from refcursor into @reftable_8, @constraintname_8
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE dict_attachment_types
END

CREATE TABLE dict_attachment_types
(
    id INT NOT NULL,
    name VARCHAR(45) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT dict_attachment_types_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- dict_brands
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'dict_brands')
BEGIN
    DECLARE @reftable_9 nvarchar(60), @constraintname_9 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'dict_brands'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_9, @constraintname_9
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_9+' drop constraint '+@constraintname_9)
        FETCH NEXT from refcursor into @reftable_9, @constraintname_9
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE dict_brands
END

CREATE TABLE dict_brands
(
    id INT NOT NULL,
    code CHAR NOT NULL,
    name VARCHAR(100) NOT NULL,
    contact_email VARCHAR(100) NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT dict_brands_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- dict_damage_scale
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'dict_damage_scale')
BEGIN
    DECLARE @reftable_10 nvarchar(60), @constraintname_10 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'dict_damage_scale'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_10, @constraintname_10
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_10+' drop constraint '+@constraintname_10)
        FETCH NEXT from refcursor into @reftable_10, @constraintname_10
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE dict_damage_scale
END

CREATE TABLE dict_damage_scale
(
    id INT NOT NULL IDENTITY,
    name VARCHAR(100) NOT NULL,
    name_en VARCHAR(100) NULL,
    code VARCHAR(3) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT dict_damage_scale_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- dict_damage_spots
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'dict_damage_spots')
BEGIN
    DECLARE @reftable_11 nvarchar(60), @constraintname_11 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'dict_damage_spots'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_11, @constraintname_11
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_11+' drop constraint '+@constraintname_11)
        FETCH NEXT from refcursor into @reftable_11, @constraintname_11
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE dict_damage_spots
END

CREATE TABLE dict_damage_spots
(
    id INT NOT NULL IDENTITY,
    name VARCHAR(100) NOT NULL,
    name_en VARCHAR(100) NULL,
    code VARCHAR(3) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT dict_damage_spots_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- dict_damage_statuses
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'dict_damage_statuses')
BEGIN
    DECLARE @reftable_12 nvarchar(60), @constraintname_12 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'dict_damage_statuses'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_12, @constraintname_12
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_12+' drop constraint '+@constraintname_12)
        FETCH NEXT from refcursor into @reftable_12, @constraintname_12
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE dict_damage_statuses
END

CREATE TABLE dict_damage_statuses
(
    id INT NOT NULL IDENTITY,
    name VARCHAR(100) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT dict_damage_statuses_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- dict_damage_types
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'dict_damage_types')
BEGIN
    DECLARE @reftable_13 nvarchar(60), @constraintname_13 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'dict_damage_types'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_13, @constraintname_13
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_13+' drop constraint '+@constraintname_13)
        FETCH NEXT from refcursor into @reftable_13, @constraintname_13
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE dict_damage_types
END

CREATE TABLE dict_damage_types
(
    id INT NOT NULL IDENTITY,
    name VARCHAR(100) NOT NULL,
    name_en VARCHAR(100) NULL,
    code VARCHAR(3) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT dict_damage_types_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- dict_discards
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'dict_discards')
BEGIN
    DECLARE @reftable_14 nvarchar(60), @constraintname_14 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'dict_discards'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_14, @constraintname_14
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_14+' drop constraint '+@constraintname_14)
        FETCH NEXT from refcursor into @reftable_14, @constraintname_14
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE dict_discards
END

CREATE TABLE dict_discards
(
    id INT NOT NULL IDENTITY,
    name VARCHAR(100) NOT NULL,
    require_comment INT DEFAULT 0 NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT dict_discards_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- dict_discounts
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_dict_discounts_dict_brands1')
    ALTER TABLE dict_discounts DROP CONSTRAINT fk_dict_discounts_dict_brands1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'dict_discounts')
BEGIN
    DECLARE @reftable_15 nvarchar(60), @constraintname_15 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'dict_discounts'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_15, @constraintname_15
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_15+' drop constraint '+@constraintname_15)
        FETCH NEXT from refcursor into @reftable_15, @constraintname_15
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE dict_discounts
END

CREATE TABLE dict_discounts
(
    id INT NOT NULL IDENTITY,
    dict_brand_id INT NOT NULL,
    cost_min INT NULL,
    cost_max INT NULL,
    discount FLOAT NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT dict_discounts_PK PRIMARY KEY (id)
);

CREATE INDEX fk_dict_discounts_dict_brands1_idx ON dict_discounts (dict_brand_id);

BEGIN
ALTER TABLE dict_discounts ADD CONSTRAINT fk_dict_discounts_dict_brands1 FOREIGN KEY (dict_brand_id) REFERENCES dict_brands (id)
END
;

-----------------------------------------------------------------------
-- dict_note_type_definitions
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'dict_note_type_definitions')
BEGIN
    DECLARE @reftable_16 nvarchar(60), @constraintname_16 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'dict_note_type_definitions'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_16, @constraintname_16
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_16+' drop constraint '+@constraintname_16)
        FETCH NEXT from refcursor into @reftable_16, @constraintname_16
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE dict_note_type_definitions
END

CREATE TABLE dict_note_type_definitions
(
    id INT NOT NULL,
    name VARCHAR(45) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT dict_note_type_definitions_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- dict_perpetrators
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'dict_perpetrators')
BEGIN
    DECLARE @reftable_17 nvarchar(60), @constraintname_17 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'dict_perpetrators'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_17, @constraintname_17
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_17+' drop constraint '+@constraintname_17)
        FETCH NEXT from refcursor into @reftable_17, @constraintname_17
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE dict_perpetrators
END

CREATE TABLE dict_perpetrators
(
    id INT NOT NULL IDENTITY,
    name VARCHAR(100) NOT NULL,
    transport_type VARCHAR(45) NULL,
    email VARCHAR(100) NOT NULL,
    phone_no VARCHAR(20) NULL,
    website VARCHAR(45) NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT dict_perpetrators_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- dict_statuses
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'dict_statuses')
BEGIN
    DECLARE @reftable_18 nvarchar(60), @constraintname_18 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'dict_statuses'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_18, @constraintname_18
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_18+' drop constraint '+@constraintname_18)
        FETCH NEXT from refcursor into @reftable_18, @constraintname_18
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE dict_statuses
END

CREATE TABLE dict_statuses
(
    id INT NOT NULL IDENTITY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT dict_statuses_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- email_templates
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'email_templates')
BEGIN
    DECLARE @reftable_19 nvarchar(60), @constraintname_19 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'email_templates'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_19, @constraintname_19
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_19+' drop constraint '+@constraintname_19)
        FETCH NEXT from refcursor into @reftable_19, @constraintname_19
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE email_templates
END

CREATE TABLE email_templates
(
    id INT NOT NULL,
    template VARCHAR(MAX) NOT NULL,
    language VARCHAR(2) DEFAULT 'pl' NOT NULL,
    code VARCHAR(10) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT email_templates_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- estimated_budgets
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_estimated_budgets_car_damages1')
    ALTER TABLE estimated_budgets DROP CONSTRAINT fk_estimated_budgets_car_damages1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_estimated_budgets_attachments1')
    ALTER TABLE estimated_budgets DROP CONSTRAINT fk_estimated_budgets_attachments1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'estimated_budgets')
BEGIN
    DECLARE @reftable_20 nvarchar(60), @constraintname_20 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'estimated_budgets'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_20, @constraintname_20
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_20+' drop constraint '+@constraintname_20)
        FETCH NEXT from refcursor into @reftable_20, @constraintname_20
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE estimated_budgets
END

CREATE TABLE estimated_budgets
(
    id INT NOT NULL IDENTITY,
    car_damage_id INT NOT NULL,
    attachment_id INT NOT NULL,
    value DECIMAL NOT NULL,
    discount_flag TINYINT DEFAULT 0 NOT NULL,
    discount_value DECIMAL NULL,
    discount_percentage DECIMAL NULL,
    car_value DECIMAL NULL,
    deduction_value DECIMAL NULL,
    is_default TINYINT DEFAULT 0 NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT estimated_budgets_PK PRIMARY KEY (id)
);

CREATE INDEX fk_estimated_budgets_car_damages1_idx ON estimated_budgets (car_damage_id);

CREATE INDEX fk_estimated_budgets_attachments1_idx ON estimated_budgets (attachment_id);

BEGIN
ALTER TABLE estimated_budgets ADD CONSTRAINT fk_estimated_budgets_car_damages1 FOREIGN KEY (car_damage_id) REFERENCES car_damages (id)
END
;

BEGIN
ALTER TABLE estimated_budgets ADD CONSTRAINT fk_estimated_budgets_attachments1 FOREIGN KEY (attachment_id) REFERENCES attachments (id)
END
;

-----------------------------------------------------------------------
-- invoices
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_invoices_car_damages1')
    ALTER TABLE invoices DROP CONSTRAINT fk_invoices_car_damages1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'invoices')
BEGIN
    DECLARE @reftable_21 nvarchar(60), @constraintname_21 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'invoices'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_21, @constraintname_21
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_21+' drop constraint '+@constraintname_21)
        FETCH NEXT from refcursor into @reftable_21, @constraintname_21
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE invoices
END

CREATE TABLE invoices
(
    id INT NOT NULL IDENTITY,
    car_damage_id INT NOT NULL,
    number VARCHAR(45) NULL,
    value DECIMAL NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT invoices_PK PRIMARY KEY (id)
);

CREATE INDEX fk_invoices_car_damages1_idx ON invoices (car_damage_id);

BEGIN
ALTER TABLE invoices ADD CONSTRAINT fk_invoices_car_damages1 FOREIGN KEY (car_damage_id) REFERENCES car_damages (id)
END
;

-----------------------------------------------------------------------
-- list_fields
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'list_fields')
BEGIN
    DECLARE @reftable_22 nvarchar(60), @constraintname_22 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'list_fields'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_22, @constraintname_22
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_22+' drop constraint '+@constraintname_22)
        FETCH NEXT from refcursor into @reftable_22, @constraintname_22
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE list_fields
END

CREATE TABLE list_fields
(
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT list_fields_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- list_filters
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'list_filters')
BEGIN
    DECLARE @reftable_23 nvarchar(60), @constraintname_23 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'list_filters'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_23, @constraintname_23
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_23+' drop constraint '+@constraintname_23)
        FETCH NEXT from refcursor into @reftable_23, @constraintname_23
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE list_filters
END

CREATE TABLE list_filters
(
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_modified DATETIME NULL,
    CONSTRAINT list_filters_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- notes
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_notes_car_damages1')
    ALTER TABLE notes DROP CONSTRAINT fk_notes_car_damages1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_notes_dict_note_type_definitions1')
    ALTER TABLE notes DROP CONSTRAINT fk_notes_dict_note_type_definitions1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'notes')
BEGIN
    DECLARE @reftable_24 nvarchar(60), @constraintname_24 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'notes'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_24, @constraintname_24
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_24+' drop constraint '+@constraintname_24)
        FETCH NEXT from refcursor into @reftable_24, @constraintname_24
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE notes
END

CREATE TABLE notes
(
    id INT NOT NULL IDENTITY,
    car_damage_id INT NOT NULL,
    dict_note_type_definition_id INT NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    number VARCHAR(45) NULL,
    value DECIMAL NULL,
    CONSTRAINT notes_PK PRIMARY KEY (id)
);

CREATE INDEX fk_notes_car_damages1_idx ON notes (car_damage_id);

CREATE INDEX fk_notes_dict_note_type_definitions1_idx ON notes (dict_note_type_definition_id);

BEGIN
ALTER TABLE notes ADD CONSTRAINT fk_notes_car_damages1 FOREIGN KEY (car_damage_id) REFERENCES car_damages (id)
END
;

BEGIN
ALTER TABLE notes ADD CONSTRAINT fk_notes_dict_note_type_definitions1 FOREIGN KEY (dict_note_type_definition_id) REFERENCES dict_note_type_definitions (id)
END
;

-----------------------------------------------------------------------
-- permission_list_fields
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_permissions_has_list_fields_permissions1')
    ALTER TABLE permission_list_fields DROP CONSTRAINT fk_permissions_has_list_fields_permissions1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_permissions_has_list_fields_list_fields1')
    ALTER TABLE permission_list_fields DROP CONSTRAINT fk_permissions_has_list_fields_list_fields1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'permission_list_fields')
BEGIN
    DECLARE @reftable_25 nvarchar(60), @constraintname_25 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'permission_list_fields'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_25, @constraintname_25
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_25+' drop constraint '+@constraintname_25)
        FETCH NEXT from refcursor into @reftable_25, @constraintname_25
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE permission_list_fields
END

CREATE TABLE permission_list_fields
(
    permissions_id INT NOT NULL,
    list_fields_id INT NOT NULL,
    CONSTRAINT permission_list_fields_PK PRIMARY KEY (permissions_id,list_fields_id)
);

CREATE INDEX fk_permissions_has_list_fields_list_fields1_idx ON permission_list_fields (list_fields_id);

CREATE INDEX fk_permissions_has_list_fields_permissions1_idx ON permission_list_fields (permissions_id);

BEGIN
ALTER TABLE permission_list_fields ADD CONSTRAINT fk_permissions_has_list_fields_permissions1 FOREIGN KEY (permissions_id) REFERENCES permissions (id)
END
;

BEGIN
ALTER TABLE permission_list_fields ADD CONSTRAINT fk_permissions_has_list_fields_list_fields1 FOREIGN KEY (list_fields_id) REFERENCES list_fields (id)
END
;

-----------------------------------------------------------------------
-- permissions
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'permissions')
BEGIN
    DECLARE @reftable_26 nvarchar(60), @constraintname_26 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'permissions'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_26, @constraintname_26
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_26+' drop constraint '+@constraintname_26)
        FETCH NEXT from refcursor into @reftable_26, @constraintname_26
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE permissions
END

CREATE TABLE permissions
(
    id INT NOT NULL IDENTITY,
    code VARCHAR(10) NOT NULL,
    name VARCHAR(100) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT permissions_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- permissions_list_filters
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_permissions_has_list_filters_permissions1')
    ALTER TABLE permissions_list_filters DROP CONSTRAINT fk_permissions_has_list_filters_permissions1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_permissions_has_list_filters_list_filters1')
    ALTER TABLE permissions_list_filters DROP CONSTRAINT fk_permissions_has_list_filters_list_filters1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'permissions_list_filters')
BEGIN
    DECLARE @reftable_27 nvarchar(60), @constraintname_27 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'permissions_list_filters'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_27, @constraintname_27
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_27+' drop constraint '+@constraintname_27)
        FETCH NEXT from refcursor into @reftable_27, @constraintname_27
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE permissions_list_filters
END

CREATE TABLE permissions_list_filters
(
    permissions_id INT NOT NULL,
    list_filters_id INT NOT NULL,
    CONSTRAINT permissions_list_filters_PK PRIMARY KEY (permissions_id,list_filters_id)
);

CREATE INDEX fk_permissions_has_list_filters_list_filters1_idx ON permissions_list_filters (list_filters_id);

CREATE INDEX fk_permissions_has_list_filters_permissions1_idx ON permissions_list_filters (permissions_id);

BEGIN
ALTER TABLE permissions_list_filters ADD CONSTRAINT fk_permissions_has_list_filters_permissions1 FOREIGN KEY (permissions_id) REFERENCES permissions (id)
END
;

BEGIN
ALTER TABLE permissions_list_filters ADD CONSTRAINT fk_permissions_has_list_filters_list_filters1 FOREIGN KEY (list_filters_id) REFERENCES list_filters (id)
END
;

-----------------------------------------------------------------------
-- process_type_actions
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_process_types_has_actions_process_types1')
    ALTER TABLE process_type_actions DROP CONSTRAINT fk_process_types_has_actions_process_types1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_process_types_has_actions_actions1')
    ALTER TABLE process_type_actions DROP CONSTRAINT fk_process_types_has_actions_actions1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'process_type_actions')
BEGIN
    DECLARE @reftable_28 nvarchar(60), @constraintname_28 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'process_type_actions'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_28, @constraintname_28
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_28+' drop constraint '+@constraintname_28)
        FETCH NEXT from refcursor into @reftable_28, @constraintname_28
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE process_type_actions
END

CREATE TABLE process_type_actions
(
    process_type_id INT NOT NULL,
    action_id INT NOT NULL,
    CONSTRAINT process_type_actions_PK PRIMARY KEY (process_type_id,action_id)
);

CREATE INDEX fk_process_types_has_actions_actions1_idx ON process_type_actions (action_id);

CREATE INDEX fk_process_types_has_actions_process_types1_idx ON process_type_actions (process_type_id);

BEGIN
ALTER TABLE process_type_actions ADD CONSTRAINT fk_process_types_has_actions_process_types1 FOREIGN KEY (process_type_id) REFERENCES process_types (id)
END
;

BEGIN
ALTER TABLE process_type_actions ADD CONSTRAINT fk_process_types_has_actions_actions1 FOREIGN KEY (action_id) REFERENCES actions (id)
END
;

-----------------------------------------------------------------------
-- process_type_actions_permissions
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_process_type_actions_has_process_type_permissions_process_1')
    ALTER TABLE process_type_actions_permissions DROP CONSTRAINT fk_process_type_actions_has_process_type_permissions_process_1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_process_type_actions_has_process_type_permissions_process_2')
    ALTER TABLE process_type_actions_permissions DROP CONSTRAINT fk_process_type_actions_has_process_type_permissions_process_2;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'process_type_actions_permissions')
BEGIN
    DECLARE @reftable_29 nvarchar(60), @constraintname_29 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'process_type_actions_permissions'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_29, @constraintname_29
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_29+' drop constraint '+@constraintname_29)
        FETCH NEXT from refcursor into @reftable_29, @constraintname_29
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE process_type_actions_permissions
END

CREATE TABLE process_type_actions_permissions
(
    process_type_id INT NOT NULL,
    action_id INT NOT NULL,
    permission_id INT NOT NULL,
    CONSTRAINT process_type_actions_permissions_PK PRIMARY KEY (process_type_id,action_id,permission_id)
);

CREATE INDEX fk_process_type_actions_has_process_type_permissions_proces_idx ON process_type_actions_permissions (permission_id,process_type_id);

CREATE INDEX fk_process_type_actions_has_process_type_permissions_proces_idx1 ON process_type_actions_permissions (process_type_id,action_id);

BEGIN
ALTER TABLE process_type_actions_permissions ADD CONSTRAINT fk_process_type_actions_has_process_type_permissions_process_1 FOREIGN KEY (process_type_id,action_id) REFERENCES process_type_actions (process_type_id,action_id)
END
;

BEGIN
ALTER TABLE process_type_actions_permissions ADD CONSTRAINT fk_process_type_actions_has_process_type_permissions_process_2 FOREIGN KEY (permission_id,process_type_id) REFERENCES process_type_permissions (permission_id,process_type_id)
END
;

-----------------------------------------------------------------------
-- process_type_permissions
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_process_types_has_permissions_process_types1')
    ALTER TABLE process_type_permissions DROP CONSTRAINT fk_process_types_has_permissions_process_types1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_process_types_has_permissions_permissions1')
    ALTER TABLE process_type_permissions DROP CONSTRAINT fk_process_types_has_permissions_permissions1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'process_type_permissions')
BEGIN
    DECLARE @reftable_30 nvarchar(60), @constraintname_30 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'process_type_permissions'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_30, @constraintname_30
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_30+' drop constraint '+@constraintname_30)
        FETCH NEXT from refcursor into @reftable_30, @constraintname_30
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE process_type_permissions
END

CREATE TABLE process_type_permissions
(
    process_type_id INT NOT NULL,
    permission_id INT NOT NULL,
    CONSTRAINT process_type_permissions_PK PRIMARY KEY (process_type_id,permission_id)
);

CREATE INDEX fk_process_types_has_permissions_permissions1_idx ON process_type_permissions (permission_id);

CREATE INDEX fk_process_types_has_permissions_process_types1_idx ON process_type_permissions (process_type_id);

BEGIN
ALTER TABLE process_type_permissions ADD CONSTRAINT fk_process_types_has_permissions_process_types1 FOREIGN KEY (process_type_id) REFERENCES process_types (id)
END
;

BEGIN
ALTER TABLE process_type_permissions ADD CONSTRAINT fk_process_types_has_permissions_permissions1 FOREIGN KEY (permission_id) REFERENCES permissions (id)
END
;

-----------------------------------------------------------------------
-- process_types
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'process_types')
BEGIN
    DECLARE @reftable_31 nvarchar(60), @constraintname_31 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'process_types'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_31, @constraintname_31
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_31+' drop constraint '+@constraintname_31)
        FETCH NEXT from refcursor into @reftable_31, @constraintname_31
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE process_types
END

CREATE TABLE process_types
(
    id INT NOT NULL IDENTITY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT process_types_PK PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- sessions
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_sessions_users1')
    ALTER TABLE sessions DROP CONSTRAINT fk_sessions_users1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'sessions')
BEGIN
    DECLARE @reftable_32 nvarchar(60), @constraintname_32 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'sessions'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_32, @constraintname_32
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_32+' drop constraint '+@constraintname_32)
        FETCH NEXT from refcursor into @reftable_32, @constraintname_32
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE sessions
END

CREATE TABLE sessions
(
    id INT NOT NULL,
    user_id INT NOT NULL,
    key VARCHAR(64) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    active_to DATETIME NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT sessions_PK PRIMARY KEY (id)
);

CREATE INDEX fk_sessions_users1_idx ON sessions (user_id);

BEGIN
ALTER TABLE sessions ADD CONSTRAINT fk_sessions_users1 FOREIGN KEY (user_id) REFERENCES users (id)
END
;

-----------------------------------------------------------------------
-- users
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='fk_users_permissions1')
    ALTER TABLE users DROP CONSTRAINT fk_users_permissions1;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'users')
BEGIN
    DECLARE @reftable_33 nvarchar(60), @constraintname_33 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'users'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_33, @constraintname_33
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_33+' drop constraint '+@constraintname_33)
        FETCH NEXT from refcursor into @reftable_33, @constraintname_33
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE users
END

CREATE TABLE users
(
    id INT NOT NULL IDENTITY,
    permission_id INT NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    modified DATETIME NULL,
    CONSTRAINT users_PK PRIMARY KEY (id)
);

CREATE INDEX fk_users_permissions1_idx ON users (permission_id);

BEGIN
ALTER TABLE users ADD CONSTRAINT fk_users_permissions1 FOREIGN KEY (permission_id) REFERENCES permissions (id)
END
;
